<?php
require_once('db_function.php');
db_connect();


// End Global Functions -----------------------------------------------------------------

// =======================================================================================
// ----------------- Insert Tables ------------------------------------------------------
// =======================================================================================
function adminLogin($user,$pass)
{
	db_connect();
	
	$result = mysql_query("SELECT * FROM tbl_admin WHERE adm_name = '".$user."' AND adm_password = '".$pass."'");
	
	$num_rows = mysql_num_rows($result);
	
	if($num_rows < 1)   return false;
			
	mysql_free_result($result);
	return true;
}
function putMemberBasicInfo($strLoginName, $strPassword, $strMainEmail)
{
	try{
		$strLoginName = strtolower(VEmptyString($strLoginName));
		$strPassword = mysql_real_escape_string($strPassword);
		$strMainEmail = strtolower(VEmptyString($strMainEmail));
		$strClientIP = VEmptyString(getClientIP());
        $strRegisterDate = VEmptyString(getPPTime());
        $strLastLogin = "'2002-02-02 02:02:02'";

		$strVC = mysql_real_escape_string(getEmailVerifyCode()); // Array index 3 = Verify Code
		
		$sql="INSERT INTO tblMemberBasicInfo
			VALUES($strLoginName, '$strPassword', $strMainEmail, $strRegisterDate,
                $strLastLogin, $strClientIP, '$strVC', NULL, 'Verifying')";
        // return $sql;
		$result = ExecuteNonQuery($sql);

		if($result[1] > 0)
			$UserInfo = array("U"=>EncryptA($strLoginName), "P"=>EncryptA($strLoginName),
							"V"=>EncryptA($strVC));
		else
			$UserInfo = array();
			
		array_push($result, $UserInfo);
		return $result;
	}catch(Exception $e){
		throw new Exception("putBranch:". $e->getMessage());
	}
}

// =======================================================================================
// ----------------- Select Tables ------------------------------------------------------
// =======================================================================================

function getMemberBasicInfo($Lfrom, $Lto, $OrderBy, $OrderAD){
	try{
		$Fields=array('strLoginName'=>"membLoginName",
			'strPassword'=>"membPassword",
			'strMainEmail'=>"membMainEmail",
			'strClientIP'=>"membClientIP",
			'strEmailVerifyCode'=>"membEmailVerifyCode",
			'dtmVerified'=>"membVerified");
		$OrderKey=array('ASC'=>"ASC", 'DESC'=>"DESC");
        
		$sql = "SELECT COUNT(*) AS NumRow
            FROM tblMemberBasicInfo";
            
        $arrNumRow = ExecuteReader($sql);
//return $arrNumRow;
		$sql = "SELECT membLoginName AS 'Login Name',
                    membPassword AS 'Password',
                    membMainEmail AS 'Main Email',
                    membRegisterDate AS 'Register Date',
                    membLastLogin AS 'Last Login',
                    membClientIP AS 'Client IP',
                    membEmailVerifyCode AS 'Email Verifiy Code',
                    membVerified AS 'Verified',
                    membStatus AS 'Status'
				FROM tblMemberBasicInfo
				ORDER BY $Fields[$OrderBy] $OrderKey[$OrderAD]
				LIMIT $Lfrom, $Lto";
        // return $sql;
		$arrSearchResult = ExecuteReader($sql);
        //return $arrNumRow;
        array_push($arrNumRow,$arrSearchResult);
        return $arrNumRow;
    }catch(Exception $e){
		throw new Exception("getMemberBasicInfo:". $e->getMessage());
	}
}

// =======================================================================================
// ----------------- Update Tables ------------------------------------------------------
// =======================================================================================


function setMemberPassword($strLoginName, $Old_strPassword, $New_strPassword)
{
    try{
        $strLoginNameinName = VEmptyStringNotNull($strLoginName);
        $Old_strPassword = mysql_real_escape_string($Old_strPassword);
        $New_strPassword = mysql_real_escape_string($New_strPassword);

		$sql="UPDATE tblMemberBasicInfo
    		SET membPassword='$New_strPassword'
    		WHERE membLoginName='$strLoginName' AND membPassword='$Old_strPassword'";
        // return $sql;
		$result = ExecuteNonQuery($sql);
		return $result;
	}catch(Exception $e){
		throw new Exception("setMemberPassword:". $e->getMessage());
	}
}

function setMemberMainEmail($strLoginName, $Old_strMainEmail, $New_strMainEmail)
{
    try{
        $strLoginName = VEmptyStringNotNull($strLoginName);
        $Old_strMainEmail = VEmptyStringNotNull($Old_strMainEmail);
        $New_strMainEmail = VEmptyStringNotNull($New_strMainEmail);

		$sql="UPDATE tblMemberBasicInfo
    		SET membMainEmail=$New_strMainEmail
    		WHERE membLoginName=$strLoginName AND membMainEmail=$Old_strMainEmail";
        // return $sql;
		$result = ExecuteNonQuery($sql);
		return $result;
	}catch(Exception $e){
		throw new Exception("setMemberMainEmail:". $e->getMessage());
	}
}

// =======================================================================================
// ----------------- Functions ------------------------------------------------------
// =======================================================================================

function MemberLogin($strLoginName, $strPassword) { // Return User Staus
	try{
        $strLoginName = VEmptyStringNotNull($strLoginName);
        $strPassword = mysql_real_escape_string($strPassword);
		$sql="SELECT membStatus AS 'Status'
            FROM tblMemberBasicInfo
            WHERE membLoginName = LOWER($strLoginName) OR
                membMainEmail = LOWER($strLoginName) AND
                membPassword = '$strPassword'";
/*        $sql = sprintf("SELECT usrUserType
            FROM tblUser
            WHERE LOWER(usrLoginName)=LOWER('%s') AND
                usrPassword='%s' AND usr_brnName='$Branch'",
                    mysql_real_escape_string($UserName),
                    mysql_real_escape_string($Password));*/
        //return $sql;

        $result = ExecuteReader($sql);
        //return $result;
		//=ExecuteReader($sql);
		if(empty($result)){
			$status = array('Login Result'=>"0");
            $result = array("");   // avoid null
        }else{
			$status = array('Login Result'=>"1");
        }

        array_push($status, $result[0]);
        return $status;
			//return mysql_num_rows($result);
	}catch(Exception $e){
		throw new Exception("Login:". $e->getMessage());
	}
}

function ChangeSuperAdminPassword($Password){
	try{
		$sql="UPDATE tblUser
            SET usrPassword='$Password'
            WHERE LOWER(usrLoginName)=LOWER('SuperAdmin') AND
                usrUserType='Super Administrator' AND
                usr_brnName='Headquarter'";
        // return $sql;
        $param = array('sql'=>$sql);
		return $GLOBALS["client"]->call('ExecuteNonQuery', $param);
	}catch(Exception $e){
		throw new Exception("ChangeSuperAdminPassword:". $e->getMessage());
	}
}

function VerifyEmailVCode($strLoginName, $strPassword, $strEmailVerifyCode)
{
    try{
        $strLoginName = VEmptyStringNotNull($strLoginName);
        $strPassword = VEmptyStringNotNull($strPassword);
        $strEmailVerifyCode = VEmptyStringNotNull($strEmailVerifyCode);

		$strLoginName = DecryptA($strLoginName);
        $strPassword = DecryptA($strPassword);
        $strEmailVerifyCode = DecryptA($strEmailVerifyCode);
		
		$sql="SELECT membStatus AS 'Status'
            FROM tblMemberBasicInfo
            WHERE membLoginName = LOWER($strLoginName) AND
                membPassword = $strPassword AND
				membEmailVerifyCode = $strEmailVerifyCode";
        //return $sql;
        $result = ExecuteReader($sql);

        if(!empty($result))
     		if($result[0]["Status"] == "Verifying"){
              $dtmVerified = VEmptyString(getPPTime());
              $sql="UPDATE tblMemberBasicInfo
                    SET membVerified = $dtmVerified
                    WHERE membLoginName=LOWER($strLoginName)";
              //return $sql;
              $result = ExecuteNonQuery($sql);

              return ($result[1] > 0)? "Success" : "Fail";
              }
            else{
                $result = "0";
            }
        else
            $result = "-1";

		return $result;
	}catch(Exception $e){
		throw new Exception("VerifyEmailVCode:". $e->getMessage());
	}
}

// ======================================================================================
// Varlidation
// ============

function validateEmail($strEmail) {
	try{
        $strEmail = VEmptyStringNotNull($strEmail);
		$sql="SELECT membMainEmail AS 'Email'
            FROM tblMemberBasicInfo
            WHERE membMainEmail = LOWER($strEmail)";
/*        $sql = sprintf("SELECT usrUserType
            FROM tblUser
            WHERE LOWER(usrLoginName)=LOWER('%s') AND
                usrPassword='%s' AND usr_brnName='$Branch'",
                    mysql_real_escape_string($UserName),
                    mysql_real_escape_string($Password));*/
        //return $sql;

        $result = ExecuteReader($sql);
        //if(empty($result))
        //    return "0";
        return empty($result)? "1" : "0";
			//return mysql_num_rows($result);
	}catch(Exception $e){
		throw new Exception("Login:". $e->getMessage());
	}
}

function validateLoginName($strLoginName) {
	try{
        $strLoginName = VEmptyStringNotNull($strLoginName);
		$sql="SELECT membLoginName AS 'Email'
            FROM tblMemberBasicInfo
            WHERE membLoginName = LOWER($strLoginName)";
/*        $sql = sprintf("SELECT usrUserType
            FROM tblUser
            WHERE LOWER(usrLoginName)=LOWER('%s') AND
                usrPassword='%s' AND usr_brnName='$Branch'",
                    mysql_real_escape_string($UserName),
                    mysql_real_escape_string($Password));*/
        //return $sql;

        $result = ExecuteReader($sql);

        return empty($result)? "1" : "0";
			//return mysql_num_rows($result);
	}catch(Exception $e){
		throw new Exception("Login:". $e->getMessage());
	}
}
// =======================================================================================
// ----------------- Non DB Functions ----------------------------------------------------
// =======================================================================================
function getEmailVerifyCode(){
	try{
		$strVC = "123";
        // return $sql;
		return $strVC;
	}catch(Exception $e){
		throw new Exception("getEmailVerifyCode:". $e->getMessage());
	}
}

function EncryptA($strText){
	try{
		$strEnc = $strText;
        // return $sql;
		return $strEnc;
	}catch(Exception $e){
		throw new Exception("EncryptA:". $e->getMessage());
	}
}

function DecryptA($strText){
	try{
		$strEnc = $strText;
        // return $sql;
		return $strEnc;
	}catch(Exception $e){
		throw new Exception("getEmailVerifyCode:". $e->getMessage());
	}
}

?>